# -!- coding: utf-8 -!-
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
根据渠道资金系统每个节点时间，计算每个节点的耗时
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

import pymysql
from datetime import datetime

db = pymysql.connect(
    host="10.16.20.182",
    user="dev",
    password="dev",
    port=3306,
    use_unicode=True,
    charset="utf8",
    database="qdzjmsloandb")

cursor = db.cursor()


def createTable():
    sql1 = 'drop table if exists zeng_Time;'
    sql2 = 'create table zeng_Time(caseID VARCHAR(32),process_no VARCHAR(32),并发数量 int,并发时间秒 float,进件时间 VARCHAR(15),审批时间 VARCHAR(15),' \
           '合同队列时间 VARCHAR(15),签合同时间 VARCHAR(15),发起支付时间 VARCHAR(15),PRIMARY KEY (caseID));'
    cursor.execute(sql1)
    cursor.execute(sql2)
    db.commit()



def insert_data(caseID,process_no,binfa_num,binfa_time,in_time,aud_time,queue_time,sign_time,pay_time):
        sql = "insert into zeng_Time (caseID,process_no,并发数量,并发时间秒,进件时间,审批时间,合同队列时间,签合同时间,发起支付时间) values" \
              " ('%s','%s','%s','%s','%s','%s','%s','%s','%s');" \
              % (caseID,process_no,binfa_num,binfa_time,in_time,aud_time,queue_time,sign_time,pay_time)
        try:
            cursor.execute(sql)
        except Exception as e:
            # 发生错误时回滚
            db.rollback()
            print(str(e))
        else:
            db.commit()  # 事务提交


def get_Time():
    sql = 'select * from zeng_test200;'
    cursor.execute(sql)
    results = cursor.fetchall()
    for i in range(len(results)):
        caseID = results[i][0]
        process_no = results[i][1]
        start_time = results[i][2]
        case_time = results[i][3]
        audit_time = results[i][4]
        queue_time = results[i][5]
        contract_time = results[i][6]
        order_time = results[i][7]
        if start_time =='' or case_time =='' or audit_time =='' or contract_time =='' or order_time =='':
            insert_data(caseID, process_no, 200, 2.81, '', '','', '', '')
        else:
            in_time = datetime.strptime(case_time, "%Y-%m-%d %H:%M:%S") - datetime.strptime(start_time[0:19], "%Y-%m-%d %H:%M:%S")
            aud_time = datetime.strptime(audit_time, "%Y-%m-%d %H:%M:%S") - datetime.strptime(case_time,"%Y-%m-%d %H:%M:%S")
            que_time = datetime.strptime(queue_time, "%Y-%m-%d %H:%M:%S") - datetime.strptime(audit_time,"%Y-%m-%d %H:%M:%S")
            sign_time = datetime.strptime(contract_time, "%Y-%m-%d %H:%M:%S") - datetime.strptime(queue_time,"%Y-%m-%d %H:%M:%S")
            pay_time = datetime.strptime(order_time, "%Y-%m-%d %H:%M:%S") - datetime.strptime(contract_time, "%Y-%m-%d %H:%M:%S")
            print (caseID,process_no,start_time,case_time,audit_time,queue_time,contract_time,order_time)
            insert_data(caseID, process_no, 200,2.81,in_time, aud_time,que_time,sign_time, pay_time)


if __name__ == '__main__':
    #createTable()
    get_Time()